/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * DBServerQuery.java
 *
 * Created on May 10, 2012, 12:30:47 PM
 */
package db.server.GUI;

import db.server.BUS.QueryBUS;
import db.server.DTO.DatabaseDTO;
import db.server.DTO.DesignTableDTO;
import db.server.DTO.EditTableDTO;
import db.server.DTO.QueryDTO;

import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import java.awt.event.ItemEvent;
import java.awt.event.ActionEvent;
import java.awt.event.ItemListener;

import javax.swing.*;
import javax.swing.ActionMap;
import javax.swing.InputMap;
import javax.swing.KeyStroke;
import javax.swing.SwingUtilities;
import javax.swing.event.DocumentEvent;
import javax.swing.event.DocumentListener;
import javax.swing.table.DefaultTableModel;
import javax.swing.text.BadLocationException;

import javax.xml.parsers.ParserConfigurationException;

/**
 *
 * @author QuangKhai
 */
public final class DBServerQuery extends javax.swing.JPanel implements DocumentListener{      
    private QueryBUS queryBUS = new QueryBUS();
    private QueryDTO query = new QueryDTO();
    private DefaultTableModel tableResult = new DefaultTableModel();            
    private List<DatabaseDTO> database = new ArrayList<DatabaseDTO>();
    
    private JTabbedPane tabpane;
    public String savePath = "";
    
    private static final String COMMIT_ACTION = "commit";
    private static enum Mode { INSERT, COMPLETION };
    private List<String> words = new ArrayList<String>();
    private Mode mode = Mode.INSERT;
    private InputMap im;
    private ActionMap am;
    
    /** Creates new form DBServerQuery */
    public DBServerQuery(QueryDTO query, List<DatabaseDTO> database, JTabbedPane tabpane) {
        initComponents();
        this.query = query;
        this.tableResult = (DefaultTableModel) resulttable.getModel();    
        this.tabpane = tabpane;
        this.database = database;
        
        setAuto();
        showQueryFile();        
    }
    
    ///read query file to show on text area
    public void showQueryFile(){
        for(int i = 0; i < database.size(); i++){
            if(database.get(i).getDatabaseName().compareTo(query.getDatabaseName()) == 0){
                cbb_database.setSelectedIndex(i);
            }
        }        
        
        for(int i = 0; i < query.getQuerySentences().size(); i++){
            sqlquerytext.append(query.getQuerySentences().get(i) + "\n");            
        }
    }
    
    /** Creates new form DBServerQuery */
    public DBServerQuery(List<DatabaseDTO> database, JTabbedPane tabpane){        
        initComponents();
        this.database = database;
        this.tableResult = (DefaultTableModel) resulttable.getModel();    
        this.tabpane = tabpane;
        
        setAuto();
    }
    
    ///Support call execute query from outside
    public void execute(){
        ActionEvent evt = null;
        bt_excuteActionPerformed(evt);
    }
    
    ///Support call save query from outside
    public void save(){
        ActionEvent evt = null;
        bt_saveActionPerformed(evt);
    }   
    
    ///Set up autocompete
    public void setAuto(){
        im = sqlquerytext.getInputMap();
        am = sqlquerytext.getActionMap();
        im.put(KeyStroke.getKeyStroke("ENTER"), COMMIT_ACTION);
        am.put(COMMIT_ACTION, new CommitAction());
        sqlquerytext.getDocument().addDocumentListener(this);
        
        for(int i = 0; i < database.size(); i++){
            cbb_database.addItem(database.get(i).getDatabaseName());
        }
        
        addWords();
        
        cbb_database.addItemListener(new ItemListener() {

            @Override
            public void itemStateChanged(ItemEvent e) {
                addWords();                
            }
        });
    }
    
    @Override 
    public void insertUpdate(DocumentEvent e) {
        if (e.getLength() != 1) {
            return;
        }

        int pos = e.getOffset();
        String content = null;
        try {
            content = sqlquerytext.getText(0, pos + 1);
        } catch (BadLocationException ex) {
            Logger.getLogger(DBServerQuery.class.getName()).log(Level.SEVERE, null, ex);
        }


        // Find where the word starts
        int w;
        for (w = pos; w >= 0; w--) {
            if (! Character.isLetter(content.charAt(w))) {
                break;
            }
        }
        if (pos - w < 2) {
            // Too few chars
            return;
        }

        String prefix = content.substring(w + 1);

        int n = 0;
        for(int j = 0; j < words.size(); j++){
            if(words.get(j).contains(prefix)){
                n = -(j + 1);
                break;
            }
        }

        if (n < 0 && -n <= words.size()) {
            String match = words.get(-n - 1);
            if (match.startsWith(prefix)) {
                // A completion is found
                String completion = match.substring(pos - w);
                // We cannot modify Document from within notification,
                // so we submit a task that does the change later
                SwingUtilities.invokeLater(
                        new CompletionTask(completion, pos + 1));
            }
        } else {
            // Nothing found
            mode = Mode.INSERT;
        }
    }


    @Override
    public void removeUpdate(DocumentEvent e) {                
    }


    @Override
    public void changedUpdate(DocumentEvent e) {                
    }
            
    ///Add key words
    public void addWords(){
        words.clear();
        
        words.add("SELECT ");
        words.add("FROM ");
        words.add("WHERE ");
        words.add("INSERT INTO ");
        words.add("UPDATE ");
        words.add("DELETE FROM ");   
        words.add("GROUP BY ");   
        words.add("SUM ");
        words.add("MIN ");
        words.add("MAX ");
        words.add("AVG ");
        words.add("Count ");
        
        int index = cbb_database.getSelectedIndex();
        DatabaseDTO databaseDTO = new DatabaseDTO();
        
        databaseDTO = database.get(index);
        
        words.add(databaseDTO.getDatabaseName());
        
        index = databaseDTO.getLstTableDTO().size();
        for(int i = 0; i < index; i++){
            DesignTableDTO tableDTO = new DesignTableDTO();
            tableDTO = databaseDTO.getLstTableDTO().get(i);
            words.add(tableDTO.getTableName());
            
            int size = tableDTO.getLstRows().size();
            for(int j = 0; j < size; j++){
                words.add(tableDTO.getLstRows().get(j).getColumnName());
            }
        }    
    }

    private class CommitAction extends AbstractAction {
        @Override
        public void actionPerformed(ActionEvent ev) {
            if (mode == Mode.COMPLETION) {
                int pos = sqlquerytext.getSelectionEnd();
                sqlquerytext.insert(" ", pos);
                sqlquerytext.setCaretPosition(pos);
                mode = Mode.INSERT;
            } else {
                sqlquerytext.replaceSelection("\n");
            }
        }
    }
    
    private class CompletionTask implements Runnable {
        String completion;
        int position;
        
        CompletionTask(String completion, int position) {
            this.completion = completion;
            this.position = position;
        }
        
        @Override
        public void run() {
            sqlquerytext.insert(completion, position);
            sqlquerytext.setCaretPosition(position + completion.length());
            sqlquerytext.moveCaretPosition(position);
            mode = Mode.COMPLETION;
        }
    }
    
    ///Set result values into table and show message
    public void setResultValue(EditTableDTO editTableDTO){       
        if(editTableDTO == null){
            messagestext.setText("!!!");
            return;
        }
        
        for(int i = 0; i < editTableDTO.getColumnName().size(); i++){  
            tableResult.addColumn(editTableDTO.getColumnName().get(i));
        }
        
        int columns = editTableDTO.getColumnName().size();
        int rows = editTableDTO.getLstValues().size();
        
        for(int i = 0; i < rows; i++){
            tableResult.insertRow(resulttable.getRowCount(), new Object[]{});    
            for(int j = 0; j < columns; j++){                
                tableResult.setValueAt(editTableDTO.getLstValues().get(i).get(j), i, j);                                                
            }                
        }
        
        messagestext.setText(rows + " rows affected!");
    }
    
    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jScrollPane2 = new javax.swing.JScrollPane();
        jTable1 = new javax.swing.JTable();
        jScrollPane1 = new javax.swing.JScrollPane();
        sqlquerytext = new javax.swing.JTextArea();
        jTabbedPane1 = new javax.swing.JTabbedPane();
        jPanel1 = new javax.swing.JPanel();
        jScrollPane3 = new javax.swing.JScrollPane();
        resulttable = new javax.swing.JTable();
        jPanel2 = new javax.swing.JPanel();
        jScrollPane4 = new javax.swing.JScrollPane();
        messagestext = new javax.swing.JTextArea();
        bt_excute = new javax.swing.JButton();
        bt_cancel = new javax.swing.JButton();
        bt_save = new javax.swing.JButton();
        jLabel1 = new javax.swing.JLabel();
        cbb_database = new javax.swing.JComboBox();

        jTable1.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "Title 1", "Title 2", "Title 3", "Title 4"
            }
        ));
        jScrollPane2.setViewportView(jTable1);

        setAutoscrolls(true);
        setFocusCycleRoot(true);
        setFocusTraversalPolicyProvider(true);
        setInheritsPopupMenu(true);
        setPreferredSize(new java.awt.Dimension(580, 310));

        sqlquerytext.setColumns(20);
        sqlquerytext.setLineWrap(true);
        sqlquerytext.setRows(5);
        sqlquerytext.setWrapStyleWord(true);
        sqlquerytext.setInheritsPopupMenu(true);
        jScrollPane1.setViewportView(sqlquerytext);

        jTabbedPane1.setAutoscrolls(true);
        jTabbedPane1.setOpaque(true);

        resulttable.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {

            }
        ));
        jScrollPane3.setViewportView(resulttable);

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane3, javax.swing.GroupLayout.DEFAULT_SIZE, 482, Short.MAX_VALUE)
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane3, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, 87, Short.MAX_VALUE)
        );

        jTabbedPane1.addTab("Results", jPanel1);

        messagestext.setColumns(20);
        messagestext.setRows(5);
        messagestext.setDoubleBuffered(true);
        jScrollPane4.setViewportView(messagestext);

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane4, javax.swing.GroupLayout.DEFAULT_SIZE, 482, Short.MAX_VALUE)
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane4, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, 87, Short.MAX_VALUE)
        );

        jTabbedPane1.addTab("Messages", jPanel2);

        bt_excute.setFont(new java.awt.Font("Cambria", 0, 12)); // NOI18N
        bt_excute.setText("Excute");
        bt_excute.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                bt_excuteActionPerformed(evt);
            }
        });

        bt_cancel.setFont(new java.awt.Font("Cambria", 0, 12)); // NOI18N
        bt_cancel.setText("Cancel");
        bt_cancel.setFocusable(false);
        bt_cancel.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                bt_cancelActionPerformed(evt);
            }
        });

        bt_save.setFont(new java.awt.Font("Cambria", 0, 12)); // NOI18N
        bt_save.setText("Save");
        bt_save.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                bt_saveActionPerformed(evt);
            }
        });

        jLabel1.setFont(new java.awt.Font("Cambria", 0, 12)); // NOI18N
        jLabel1.setText("Database");

        cbb_database.setFont(new java.awt.Font("Cambria", 0, 12)); // NOI18N

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(jTabbedPane1, javax.swing.GroupLayout.Alignment.LEADING, 0, 0, Short.MAX_VALUE)
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, 487, Short.MAX_VALUE)
                    .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                        .addComponent(jLabel1)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(cbb_database, javax.swing.GroupLayout.PREFERRED_SIZE, 125, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(bt_cancel)
                    .addComponent(bt_excute)
                    .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                        .addGap(2, 2, 2)
                        .addComponent(bt_save, javax.swing.GroupLayout.PREFERRED_SIZE, 65, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel1)
                    .addComponent(cbb_database, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(bt_excute)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(bt_save)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(bt_cancel))
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 135, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jTabbedPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 115, Short.MAX_VALUE)))
                .addContainerGap())
        );
    }// </editor-fold>//GEN-END:initComponents

private void bt_excuteActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bt_excuteActionPerformed
// TODO add your handling code here:    
    String queryText = "";    
    String queryArray[] = sqlquerytext.getText().split("\\n");
    
    for(int i = 0; i < queryArray.length; i++){
        queryText = queryText + queryArray[i] + "";
    }
    
    int index = cbb_database.getSelectedIndex();
    DatabaseDTO databaseDTO = new DatabaseDTO();        
    databaseDTO = database.get(index);

    tableResult.setColumnCount(0);
    tableResult.setRowCount(0);
    
    String check = queryArray[0].split(" ")[0];
    
    
    if(check.compareTo("SELECT") == 0){
        setResultValue(queryBUS.getSelectResults(queryText, databaseDTO));
    }else if(check.compareTo("DELETE") == 0){
        try {
            if(queryBUS.getDeleteResults(queryText, databaseDTO) == null){
                messagestext.setText("All rows in table affected!");
            }else{
                setResultValue(queryBUS.getDeleteResults(queryText, databaseDTO));
            }
        } catch (ParserConfigurationException ex) {
            Logger.getLogger(DBServerQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }else if(check.compareTo("INSERT") == 0){
            try {
                setResultValue(queryBUS.getInsertResults(queryText, databaseDTO));
            } catch (ParserConfigurationException ex) {
                Logger.getLogger(DBServerQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
    } else if(check.compareTo("UPDATE") == 0){
            try {
                setResultValue(queryBUS.getUpdateResults(queryText, databaseDTO));
            } catch (ParserConfigurationException ex) {
                Logger.getLogger(DBServerQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
    }
}//GEN-LAST:event_bt_excuteActionPerformed

private void bt_saveActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bt_saveActionPerformed
// TODO add your handling code here:
    List<String> querySentences = new ArrayList<String>();
    QueryDTO queryDTO = new QueryDTO();
    
    queryDTO.setDatabaseName((String)cbb_database.getSelectedItem());
    String queryArray[] = sqlquerytext.getText().split("\\n");
    
    for(int i = 0; i < queryArray.length; i++){
        querySentences.add(queryArray[i].toString());
    }
    
    queryDTO.setQuerySentences(querySentences); 
    
    if(savePath.compareTo("") == 0){
        JFileChooser fc = new JFileChooser();    
        int returnVal = fc.showSaveDialog(this);
        if (returnVal == JFileChooser.APPROVE_OPTION) {            
                try {
                    savePath = fc.getSelectedFile().toString() + ".xml";
                    queryBUS.saveQuery(queryDTO, savePath);
                } catch (ParserConfigurationException ex) {
                    Logger.getLogger(DBServerQuery.class.getName()).log(Level.SEVERE, null, ex);
                }
        }
    }else{
            try {
                queryBUS.saveQuery(queryDTO, savePath);
            } catch (ParserConfigurationException ex) {
                Logger.getLogger(DBServerQuery.class.getName()).log(Level.SEVERE, null, ex);
            }
    }
}//GEN-LAST:event_bt_saveActionPerformed

private void bt_cancelActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bt_cancelActionPerformed
// TODO add your handling code here:
    tabpane.remove(tabpane.getSelectedIndex());
}//GEN-LAST:event_bt_cancelActionPerformed

    

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton bt_cancel;
    private javax.swing.JButton bt_excute;
    private javax.swing.JButton bt_save;
    private javax.swing.JComboBox cbb_database;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JScrollPane jScrollPane3;
    private javax.swing.JScrollPane jScrollPane4;
    private javax.swing.JTabbedPane jTabbedPane1;
    private javax.swing.JTable jTable1;
    private javax.swing.JTextArea messagestext;
    private javax.swing.JTable resulttable;
    private javax.swing.JTextArea sqlquerytext;
    // End of variables declaration//GEN-END:variables
}
